package dao.entity;

import bean.Manager;
import dao.MysqlConfiguration;
import dao.interf.ManagerDao;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class ManagerDaoImpl implements ManagerDao {
    @Override
    public List<Manager> manager_find(int currentPage) {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select * from managers limit ?,?;";
        ResultSet rs;
        List<Manager> list = new ArrayList<Manager>();
        try {
            PreparedStatement pst = conn.prepareStatement(sql);
            pst.setInt(1,(currentPage-1)*5);
            pst.setInt(2,5);
            rs = pst.executeQuery();
            while (rs.next()){
                Manager m = new Manager();
                m.setMgid(rs.getString(1));
                m.setMgname(rs.getString(2));
                m.setMg_pwd(rs.getString(3));
                m.setLg_time(rs.getString(4));
                m.setStatus(rs.getString(5));
                m.setAccess(rs.getString(6));
                list.add(m);//添加当前顾客用户到列表中
            }
        } catch (Exception e) {
            System.out.println("创建语句对象失败!"+e.getMessage());
        }
        return list;
    }

    @Override
    public int addManager(Manager m) {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        //定义sql语句
        String sql = "insert into managers values(?,?,?,?,?,?)";

        int n = 0;//受影响的行数
        try {
            //创建statement语句对象
            //Statement st = conn.createStatement();
            //创建一个PrepareStatement语句对象
            PreparedStatement pst = conn.prepareStatement(sql);
            System.out.println("创建语句对象成功!");
            pst.setString(1,m.getMgid());
            pst.setString(2,m.getMgname());
            pst.setString(3,m.getMg_pwd());
            pst.setString(4,m.getLg_time());
            pst.setString(5,m.getStatus());
            pst.setString(6,m.getAccess());

            n = pst.executeUpdate();//执行语句
            System.out.println("执行语句成功!");
            //执行指定的sql语句
            //n = pst.executeUpdate();
        } catch (Exception e) {
            System.out.println("创建语句对象失败!"+e.getMessage());
        }
        return n;//返回受影响的行数
    }

    @Override
    public int manager_count() {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select count(*) from managers;";
        ResultSet rs;
        int n = 0;
        try {
            Statement st = conn.createStatement();
            rs = st.executeQuery(sql);
            while (rs.next()){
                n = rs.getInt(1);
            }
        } catch (Exception e) {
            System.out.println("创建语句对象失败! 错误是:"+e.getMessage());
        }
        return n;
    }

    @Override
    public int deleteManager(String mgid) {
        //连接数据库
        Connection conn = MysqlConfiguration.MysqlConnection();
        //定义sql语句
        String sql = "delete from managers where mgid=?";
        int n = 0;//初始化受影响的行数
        try {
            //定义预处理语句对象
            PreparedStatement pst = conn.prepareStatement(sql);
            System.out.println("创建语句对象成功");
            pst.setString(1,mgid);
            //pst.setInt(1,sno);
            n = pst.executeUpdate();//执行语句
            //System.out.println("执行语句成功");
        } catch (Exception e) {
            System.out.println("创建语句异常: "+e.getMessage());
        }
        return n;
    }

    @Override
    public int selectMgidmaxManager() {
        //连接数据库
        String mgid = "";
        int num = 0;
        Connection conn = MysqlConfiguration.MysqlConnection();
        String sql = "select  max(mgid) from managers;";
        ResultSet rs = null;
        try {
            Statement st = conn.createStatement();
            System.out.println("查询评论语句创建成功!");
            rs = st.executeQuery(sql);
            if(rs!=null){
                System.out.println("查找成功");
                rs.next();//移动光标
                System.out.println("光标移动成功");
                mgid = rs.getString(1);
                System.out.println("查询成功,mgid为"+mgid);
                mgid= mgid.substring(2);
                num = Integer.parseInt(mgid);
                System.out.println("获取成功,mgid序号为"+ num);
            }
        } catch (Exception e) {
            System.out.println("创建语句对象失败!"+e.getMessage());
        }
        return num;
    }
}
